## A.7. DBI Statement Handle Objects

This section lists the methods and attributes associated with DBI statement handles.

### A.7.1. Statement Handle Methods

The DBI defines the following methods for use on DBI statement handles:

bind_ param |

    $rc = $sth->bind_param($p_num, $bind_value) || die
    $sth->errstr;
    $rv = $sth->bind_param($p_num, $bind_value, \%attr)     || ...
    $rv = $sth->bind_param($p_num, $bind_value, $bind_type) || ...

The `bind_ param` method can be used to bind a value with a placeholder embedded in the prepared statement. Placeholders are indicated with the question mark character (`?`). For example:

>     $dbh->{RaiseError} = 1;        # save having to check each method call
>     $sth = $dbh->prepare("SELECT name, age FROM people WHERE name LIKE ?");
>     $sth->bind_param(1, "John%");  # placeholders are numbered from 1
>     $sth->execute;
>     DBI::dump_results($sth);

Note that the `?` is not enclosed in quotation marks, even when the placeholder represents a string. Some drivers also allow placeholders like `:`name and `:`n (e.g., `:1`, `:2`, and so on) in addition to `?`, but their use is not portable. Undefined bind values or `undef` can be used to indicate null values.

Some drivers do not support placeholders.

With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it. For example:

>     "SELECT name, age FROM ?"         # wrong (will probably fail)
>     "SELECT name, ?   FROM people"    # wrong (but may not 'fail')

Also, placeholders can only represent single scalar values. For example, the following statement won't work as expected for more than one value:

>     "SELECT name, age FROM people WHERE name IN (?)"    # wrong

### A.7.1.1. Datatypes for placeholders

The `\%attr` parameter can be used to hint at which datatype the placeholder should have. Typically, the driver is interested in knowing only if the placeholder should be bound as a number or a string. For example:

>     $sth->bind_param(1, $value, { TYPE => SQL_INTEGER });

As a shortcut for this common case, the datatype can be passed directly, in place of the `\%attr` hash reference. This example is equivalent to the one above:

>     $sth->bind_param(1, $value, SQL_INTEGER);

The `TYPE` value indicates the standard (non-driver-specific) type for this parameter. To specify the driver-specific type, the driver may support a driver-specific attribute, such as `{` `ora_type` `=`> `97` `}`. The datatype for a placeholder cannot be changed after the first `bind_ param` call. However, it can be left unspecified, in which case it defaults to the previous value.

Perl only has string and number scalar datatypes. All database types that aren't numbers are bound as strings and must be in a format the database will understand.

As an alternative to specifying the datatype in the `bind_ param` call, you can let the driver pass the value as the default type (`VARCHAR`). You can then use an SQL function to convert the type within the statement. For example:

>     INSERT INTO price(code, price) VALUES (?, CONVERT(MONEY,?))

The `CONVERT` function used here is just an example. The actual function and syntax will vary between different databases and is non-portable.

See also Section A.2.7, "Placeholders and Bind Values " for more information.

bind_param_inout |

    $rc = $sth->bind_param_inout($p_num, \$bind_value, $max_len) || die $sth->errstr;
    $rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, \%attr) || ...
    $rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, $bind_type) || ...

This method acts like `bind_ param` , but also enables values to be updated by the statement. The statement is typically a call to a stored procedure. The `$bind_value` must be passed as a reference to the actual value to be used.

Note that unlike `bind_ param`, the `$bind_value` variable is not read when `bind_ param_inout` is called. Instead, the value in the variable is read at the time `execute` is called.

The additional `$max_len` parameter specifies the minimum amount of memory to allocate to `$bind_value` for the new value. If the value returned from the database is too big to fit, then the execution should fail. If unsure what value to use, pick a generous length, i.e., a length larger than the longest value that would ever be returned. The only cost of using a larger value than needed is wasted memory.

It is expected that few drivers will support this method. The only driver currently known to do so is `DBD::Oracle` (`DBD::ODBC` may support it in a future release). Therefore, it should not be used for database-independent applications.

Undefined values or `undef` are used to indicate null values. See also "Placeholders and Bind Values" for more information.

execute |

    $rv = $sth->execute                || die $sth->errstr;
    $rv = $sth->execute(@bind_values)  || die $sth->errstr;

Performs whatever processing is necessary to execute the prepared statement. An `undef` is returned if an error occurs. A successful `execute` always returns true regardless of the number of rows affected, even if it's zero (see below). It is always important to check the return status of `execute` (and most other DBI methods) for errors.

For a non-`SELECT` statement, `execute` returns the number of rows affected, if known. If no rows were affected, then `execute` returns `0E0`, which Perl will treat as `0` but will regard as true. Note that it is not an error for no rows to be affected by a statement. If the number of rows affected is not known, then `execute` returns -`1`.

For `SELECT` statements, `execute` simply "starts" the query within the database engine. Use one of the fetch methods to retrieve the data after calling `execute`. The `execute` method does not return the number of rows that will be returned by the query (because most databases can't tell in advance), it simply returns a true value.

If any arguments are given, then `execute` will effectively call `bind_ param` for each value before executing the statement. Values bound in this way are usually treated as `SQL_VARCHAR` types unless the driver can determine the correct type (which is rare), or unless `bind_ param` (or `bind_ param_inout`) has already been used to specify the type.

fetchrow_arrayref |

    $ary_ref = $sth->fetchrow_arrayref;
    $ary_ref = $sth->fetch;    # alias

Fetches the next row of data and returns a reference to an array holding the field values. Null fields are returned as `undef` values in the array. This is the fastest way to fetch data, particularly if used with `$sth-`>`bind_columns`.

If there are no more rows, or if an error occurs, then `fetchrow_arrayref` returns an `undef`. You should check `$sth-`>`err` afterwards (or use the `RaiseError` attribute) to discover if the `undef` returned was due to an error.

Note that the same array reference will currently be returned for each fetch, so don't store the reference and then use it after a later fetch.

fetchrow_array |

     @ary = $sth->fetchrow_array;

An alternative to `fetchrow_arrayref` . Fetches the next row of data and returns it as a list containing the field values. Null fields are returned as `undef` values in the list.

If there are no more rows, or if an error occurs, then `fetchrow_array` returns an empty list. You should check `$sth-`>`err` afterwards (or use the `RaiseError` attribute) to discover if the empty list returned was due to an error.

In a scalar context, `fetchrow_array` returns the value of the first field. An `undef` is returned if there are no more rows or if an error occurred. Since that `undef` can't be distinguished from an `undef` returned because the first field value was NULL, you should exercise some caution if you use `fetchrow_array` in a scalar context.

fetchrow_hashref |

     $hash_ref = $sth->fetchrow_hashref;
     $hash_ref = $sth->fetchrow_hashref($name);

An alternative to `fetchrow_arrayref` . Fetches the next row of data and returns it as a reference to a hash containing field name and field value pairs. Null fields are returned as `undef` values in the hash.

If there are no more rows, or if an error occurs, then `fetchrow_hashref` returns an `undef`. You should check `$sth-`>`err` afterwards (or use the `RaiseError` attribute) to discover if the `undef` returned was due to an error.

The optional `$name` parameter specifies the name of the statement handle attribute. For historical reasons it defaults to `NAME`; however, using either `NAME_lc` or `NAME_uc` is recommended for portability.

The keys of the hash are the same names returned by `$sth-`>`{$name}`. If more than one field has the same name, there will only be one entry in the returned hash for those fields.

Because of the extra work `fetchrow_hashref` and Perl have to perform, this attribute is not as efficient as `fetchrow_arrayref` or `fetchrow_array`.

Currently, a new hash reference is returned for each row. This will change in the future to return the same hash ref each time, so don't rely on the current behavior.

fetchall_arrayref |

    $tbl_ary_ref = $sth->fetchall_arrayref;
    $tbl_ary_ref = $sth->fetchall_arrayref( $slice_array_ref );
    $tbl_ary_ref = $sth->fetchall_arrayref( $slice_hash_ref  );

The `fetchall_arrayref` method can be used to fetch all the data to be returned from a prepared and executed statement handle. It returns a reference to an array that contains one reference per row.

If there are no rows to return, `fetchall_arrayref` returns a reference to an empty array. If an error occurs, `fetchall_arrayref` returns the data fetched thus far, which may be none. You should check `$sth-`>`err` afterwards (or use the `RaiseError` attribute) to discover if the data is complete or was truncated due to an error.

When passed an array reference, `fetchall_arrayref` uses `fetchrow_arrayref` to fetch each row as an array ref. If the parameter array is not empty, then it is used as a slice to select individual columns by index number.

With no parameters, `fetchall_arrayref` acts as if passed an empty array ref.

When passed a hash reference, `fetchall_arrayref` uses `fetchrow_hashref` to fetch each row as a hash reference. If the parameter hash is not empty, then it is used as a slice to select individual columns by name. The names should be lower case regardless of the letter case in `$sth-`>`{NAME}`. The values of the hash should be set to `1`.

For example, to fetch just the first column of every row:

>     $tbl_ary_ref = $sth->fetchall_arrayref([0]);

To fetch the second to last and last column of every row:

>     $tbl_ary_ref = $sth->fetchall_arrayref([-2,-1]);

To fetch only the fields called "foo" and "bar" of every row:

>     $tbl_ary_ref = $sth->fetchall_arrayref({ foo=>1, bar=>1 });

The first two examples return a reference to an array of array refs. The last returns a reference to an array of hash refs.

finish |

    $rc  = $sth->finish;

Indicates that no more data will be fetched from this statement handle before it is either executed again or destroyed. The `finish` method is rarely needed, but can sometimes be helpful in very specific situations to allow the server to free up resources (such as sort buffers).

When all the data has been fetched from a `SELECT` statement, the driver should automatically call `finish` for you. So you should not normally need to call it explicitly.

Consider a query like:

>     SELECT foo FROM table WHERE bar=? ORDER BY foo

where you want to select just the first (smallest) "foo" value from a very large table. When executed, the database server will have to use temporary buffer space to store the sorted rows. If, after executing the handle and selecting one row, the handle won't be re-executed for some time and won't be destroyed, the `finish` method can be used to tell the server that the buffer space can be freed.

Calling `finish` resets the `Active` attribute for the statement. It may also make some statement handle attributes (such as `NAME` and `TYPE`) unavailable if they have not already been accessed (and thus cached).

The `finish` method does not affect the transaction status of the database connection. It has nothing to do with transactions. It's mostly an internal "housekeeping" method that is rarely needed. There's no need to call `finish` if you're about to destroy or re-execute the statement handle. See also `disconnect` and the `Active` attribute.

rows |

    $rv = $sth->rows;

Returns the number of rows affected by the last row-affecting command, or -`1` if the number of rows is not known or not available.

Generally, you can only rely on a row count after a non-`SELECT` `execute` (for some specific operations like `UPDATE` and `DELETE`), or after fetching all the rows of a `SELECT` statement.

For `SELECT` statements, it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far, but others may return -`1` until all rows have been fetched. So use of the `rows` method or `$DBI::rows` with `SELECT` statements is not recommended.

One alternative method to get a row count for a `SELECT` is to execute a "`SELECT` `COUNT(*)` `FROM ...`" SQL statement with the same "`...`" as your query, and then fetch the row count from that.

bind_col |

    $rc = $sth->bind_col($column_number, \$var_to_bind);

Binds an output column (field) of a `SELECT` statement to a Perl variable. See `bind_columns` for an example. Note that column numbers count up from 1.

Whenever a row is fetched from the database, the corresponding Perl variable is automatically updated. There is no need to fetch and assign the values manually. The binding is performed at a very low level using Perl aliasing, so there is no extra copying taking place. This makes using bound variables very efficient.

For maximum portability between drivers, `bind_col` should be called after `execute`. This restriction may be removed in a later version of the DBI.

You do not need to bind output columns in order to fetch data, but it can be useful for some applications that need either maximum performance or greater clarity of code. The `bind_ param` method performs a similar but opposite function for input variables.

bind_columns |

    $rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);

Calls `bind_col` for each column of the `SELECT` statement. The `bind_columns` method will die if the number of references does not match the number of fields.

For maximum portability between drivers, `bind_columns` should be called after `execute`.

For example:

>     $dbh->{RaiseError} = 1; # Do this, or check every call for errors
>     $sth = $dbh->prepare(q{ SELECT region, sales FROM sales_by_region });
>     $sth->execute;
>     my ($region, $sales);

>     # Bind Perl variables to columns:
>     $rv = $sth->bind_columns(\$region, \$sales);

>     # You can also use Perl's \(...) syntax (see perlref docs):
>     #     $sth->bind_columns(\($region, $sales));

>     # Column binding is the most efficient way to fetch data
>     while ($sth->fetch) {
>         print "$region: $sales\n";
>     }

For compatibility with old scripts, the first parameter will be ignored if it is `undef` or a hash reference.

dump_results |

    $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);

Fetches all the rows from `$sth`, calls `DBI::neat_list` for each row, and prints the results to `$fh` (defaults to `STDOUT`) separated by `$lsep` (default `"\n"`). `$fsep` defaults to `", "` and `$maxlen` defaults to `35`.

This method is designed as a handy utility for prototyping and testing queries. Since it uses `neat_list` to format and edit the string for reading by humans, it is not recomended for data transfer applications.

### A.7.2. Statement Handle Attributes

This section describes attributes specific to statement handles. Most of these attributes are read-only.

Changes to these statement handle attributes do not affect any other existing or future statement handles.

Attempting to set or get the value of an unknown attribute is fatal, except for private driver-specific attributes (which all have names starting with a lowercase letter).

For example:

>     ... = $h->{NUM_OF_FIELDS};    # get/read

Note that some drivers cannot provide valid values for some or all of these attributes until after `$sth-`>`execute` has been called.

See also `finish` to learn more about the effect it may have on some attributes.

NUM_OF_FIELDS (integer, read-only) |

Number of fields (columns) the prepared statement will return. Non-`SELECT` statements will have `NUM_OF_FIELDS == 0`.

NUM_OF_PARAMS (integer, read-only) |

The number of parameters (placeholders) in the prepared statement. See "Substitution Variables" later in this appendix for more details.

NAME (array-ref, read-only) |

Returns a reference to an array of field names for each column. The names may contain spaces but should not be truncated or have any trailing space. Note that the names have the letter case (upper, lower, or mixed) as returned by the driver being used. Portable applications should use `NAME_lc` or `NAME_uc`. For example:

>     print "First column name: $sth->{NAME}->[0]\n";

NAME_lc (array-ref, read-only) |

Like `NAME` but always returns lowercase names.

NAME_uc (array-ref, read-only) |

Like `NAME` but always returns uppercase names.

TYPE (array-ref, read-only) (NEW ) |

Returns a reference to an array of integer values for each column. The value indicates the datatype of the corresponding column.

The values correspond to the international standards (ANSI X3.135 and ISO/IEC 9075), which, in general terms, means ODBC. Driver-specific types that don't exactly match standard types should generally return the same values as an ODBC driver supplied by the makers of the database. That might include private type numbers in ranges the vendor has officially registered.

For more information, see:

ftp://jerry.ece.umassd.edu/isowg3/dbl/SQL_Registry

Where there's no vendor-supplied ODBC driver to be compatible with, the DBI driver can use type numbers in the range that is now officially reserved for use by the DBI: -9999 to -9000.

All possible values for `TYPE` should have at least one entry in the output of the `type_info_all` method (see `type_info_all`).

PRECISION (array-ref, read-only) (NEW ) |

Returns a reference to an array of integer values for each column. For non-numeric columns, the value generally refers to either the maximum length or the defined length of the column. For numeric columns, the value refers to the maximum number of significant digits used by the datatype (without considering a sign character or decimal point). Note that for floating-point types (REAL, FLOAT, DOUBLE), the "display size" can be up to seven characters greater than the precision. (for the sign + decimal point + the letter E + a sign + two or three digits).

SCALE (array-ref, read-only) (NEW ) |

Returns a reference to an array of integer values for each column. NULL (`undef`) values indicate columns where scale is not applicable.

NULLABLE (array-ref, read-only) |

Returns a reference to an array indicating the possibility of each column returning a NULL. Possible values are `0` = no, `1` = yes, `2` = unknown. For example:

>     print "First column may return NULL\n" if $sth->{NULLABLE}->[0];

CursorName (string, read-only) |

Returns the name of the cursor associated with the statement handle, if available. If not available, or if the database driver does not support the `"where` `current of` `..."` SQL syntax, then it returns `undef`.

Statement (string, read-only) (NEW ) |

Returns the statement string passed to the `prepare` method.

RowsInCache (integer, read-only) |

If the driver supports a local row cache for `SELECT` statements, then this attribute holds the number of unfetched rows in the cache. If the driver doesn't, then it returns `undef`. Note that some drivers pre-fetch rows on execute, whereas others wait till the first fetch.

See also the `RowCacheSize` database handle attribute.

Copyright © 2001 O'Reilly & Associates. All rights reserved.